<!---
	BlogFusion 4.x to MachBlog 1.0 Conversion Script
	Matt Woodward (matt@mattwoodward.com)
	4/9/2007
	
	COMMENTS:
	* This script is for MySQL only.
	* This script assumes that you have configured a datasource for BlogFusion and a datasource for MachBlog 
	  in the ColdFusion or BlueDragon administrator. The databases need not be on the same server, they just 
	  both have to be accessible in the code via a DSN.
	* This script must be run as a database user with create table rights in order to work, because it creates new 
	  tables to manage people coming to the blog using old BlogFusion URLs. This functionality also necessitates adding 
	  a block of code to your Application.cfm (see below). If you don't want to or can't use a database owner user when 
	  running this script, you may manually create two new tables prior to running this script as follows:
	
	  create table old_cat_to_new_cat(old_category_id int unsigned not null, new_category_id char(35) not null, primary key(old_category_id, new_category_id));
	  create table old_entry_to_new_entry(old_entry_id int unsigned not null, new_entry_id char(35) not null, primary key(old_entry_id, new_entry_id));
	
	* BlogFusion allows for multiple identical categories to be created. This script does NOT address this problem. The best
	  course of action is to clean up your BlogFusion data *prior* to running this script. Simply reassign any of the duplicate 
	  category IDs in the catList table to a single category ID representing the category. For example, if I have duplicate categories 
	  that are represented by IDs 38 and 26, I would reassign all the 38 IDs to 26 by executing the following query:
	  
	  update catList set listCatIDFK = 26 where listCatIDFK = 38
	  
	  Following this, delete the unused category from the categories table, which in this case would be category ID 38:
	  
	  delete from categories where catID = 38
	
	EXECUTING THIS SCRIPT:
	* Set the DSN variables to the correct values for your installation prior to running the script.
	* Set the adminID variable to the value of the user ID you wish to be shown as the creator of all the records prior to running the script.
	  Note that for items such as comments this does not apply (meaning your admin user will not be associated as the user who made the comment).
	* If you wish to support the redirect of old permalinks from BlogFusion to the new MachBlog pages, please uncomment the block of code 
	  at the bottom of Application.cfm.
	* You will likely want to run the script with a requesttimeout in the URL since it takes a bit of time for this to run. Just throw a big 
	  number in there like 10000 and you should be OK.
--->

<!--- set the respective datasources --->
<cfset blogfusionDSN = "blogfusion" />
<cfset machblogDSN = "machblog" />

<!--- need an instance of the i18n CFC to handle the date to epoch conversions --->
<cfset i18nUtils = createObject("component", "machblog.org.hastings.locale.utils").init() />

<!--- SET THIS TO THE USER ID YOU WANT ASSOCIATED WITH ALL DATABASE ACTIVITY --->
<cfset adminID = "D81731C1-A6DD-DF5A-70AB6C070E830291" />

<!--- start the fun ... --->
<h3>BlogFusion to MachBlog Conversion</h3>

Creating entry and category mapping tables ...<br />
<cfflush />

<cfquery name="createEntryMappingTable" datasource="#machblogDSN#">
	create table old_entry_to_new_entry (
					old_entry_id int unsigned not null, 
					new_entry_id char(35) not null, 
					primary key(old_entry_id, new_entry_id)
				 );
	
</cfquery>

<cfquery name="createCategoryMappingTable" datasource="#machblogDSN#">
	create table old_cat_to_new_cat (
					old_category_id int unsigned not null, 
					new_category_id char(35) not null, 
					primary key(old_category_id, new_category_id)
				  );
</cfquery>

Successfully created mapping tables.<br />
<br />

Getting categories from BlogFusion ...<br />
<cfflush />

<cfquery name="blogFusionCategories" datasource="#blogfusionDSN#">
	select 	catName, 
			catID, 
			shown
	from 	categories 
	order by catName asc
</cfquery>

Retrieved <cfoutput>#blogFusionCategories.recordCount#</cfoutput> categories from BlogFusion.<br />
<br />

Inserting BlogFusion categories into MachBlog database and populating category mapping table ...<br />
<cfflush />

<cfloop query="blogFusionCategories">
	<cfset categoryID = createUUID() />
	
	<cftransaction action="begin">
		<cfquery name="insertCategory" datasource="#machblogDSN#">
			insert into machblog_category (
				category_id, 
				category_name, 
				created_by_id, 
				dt_created, 
				ip_created, 
				is_active
			) values (
				<cfqueryparam value="#categoryID#" cfsqltype="cf_sql_char" />, 
				<cfqueryparam value="#trim(blogFusionCategories.catName)#" cfsqltype="cf_sql_varchar" />, 
				<cfqueryparam value="#adminID#" cfsqltype="cf_sql_char" />, 
				<cfqueryparam value="#i18nUtils.toEpoch(now())#" cfsqltype="cf_sql_bigint" />, 
				<cfqueryparam value="127.0.0.1" cfsqltype="cf_sql_varchar" />, 
				<cfqueryparam value="#blogFusionCategories.shown#" cfsqltype="cf_sql_tinyint" />
			)
		</cfquery>
		
		<cfquery name="insertCategoryMap" datasource="#machblogDSN#">
			insert into old_cat_to_new_cat (
				old_category_id, 
				new_category_id
			) values (
				<cfqueryparam value="#blogFusionCategories.catID#" cfsqltype="cf_sql_integer" />, 
				<cfqueryparam value="#categoryID#" cfsqltype="cf_sql_char" />
			)
		</cfquery>
	</cftransaction>
</cfloop>

BlogFusion categories migrated.<br />
<br />

Getting entries and entry categories from BlogFusion ...<br />
<cfflush />

<cfquery name="blogFusionEntries" datasource="#blogfusionDSN#">
	select 	entryID, 
			adminIDFK, 
			entryTitle, 
			entryContent, 
			entryExtended, 
			entryDate,
			entryStatus,
			entryCommentVisible,
			entryExcerpt,
			entryRSSenclosure,
			entryRSSsize, 
			entryRSStype,
			entryTBVisible, 
			entryWebID 
	from 	entries 
	order by entryDate asc
</cfquery>

<cfquery name="blogFusionEntryCats" datasource="#blogfusionDSN#">
	select 	listCatIDFK, 
			listEntryIDFK 
	from 	catList
</cfquery>

Retrieved <cfoutput>#blogFusionEntries.recordCount#</cfoutput> entries from BlogFusion.<br />
<br />

Inserting entries and entry categories into MachBlog database and populating entry mapping table ...<br />
<cfflush />

<cfloop query="blogFusionEntries">
	<cfset newEntryID = createUUID() />
	
	<cftransaction action="begin">
		<cfquery name="insertEntry" datasource="#machblogDSN#">
			insert into machblog_entry (
				entry_id, 
				title, 
				body, 
				more_body, 
				allow_comments, 
				allow_trackbacks, 
				dt_posted, 
				enclosure, 
				enclosure_mime_type, 
				enclosure_size, 
				subscriber_email_sent, 
				created_by_id, 
				dt_created, 
				ip_created, 
				is_active
			) values (
				<cfqueryparam value="#newEntryID#" cfsqltype="cf_sql_char" />, 
				<cfqueryparam value="#blogFusionEntries.entryTitle#" cfsqltype="cf_sql_varchar" />, 
				<cfqueryparam value="#blogFusionEntries.entryContent#" cfsqltype="cf_sql_longvarchar" />, 
				<cfqueryparam value="#blogFusionEntries.entryExtended#" cfsqltype="cf_sql_longvarchar" null="#not len(blogFusionEntries.entryExtended)#">, 
				<cfqueryparam value="#blogFusionEntries.entryCommentVisible#" cfsqltype="cf_sql_tinyint" />, 
				<cfqueryparam value="#blogFusionEntries.entryTBVisible#" cfsqltype="cf_sql_tinyint" />, 
				<cfqueryparam value="#i18nUtils.toEpoch(blogFusionEntries.entryDate)#" cfsqltype="cf_sql_bigint" />, 
				<cfqueryparam value="#blogFusionEntries.entryRSSenclosure#" cfsqltype="cf_sql_varchar" null="#not len(blogFusionEntries.entryRSSenclosure)#" />, 
				<cfqueryparam value="#blogFusionEntries.entryRSStype#" cfsqltype="cf_sql_varchar" null="#not len(blogFusionEntries.entryRSStype)#" />, 
				<cfqueryparam value="#blogFusionEntries.entryRSSsize#" cfsqltype="cf_sql_decimal" null="#not len(blogFusionEntries.entryRSSsize)#" />, 
				<cfqueryparam value="1" cfsqltype="cf_sql_tinyint" />, 
				<cfqueryparam value="#adminID#" cfsqltype="cf_sql_char" />, 
				<cfqueryparam value="#i18nUtils.toEpoch(now())#" cfsqltype="cf_sql_bigint" />, 
				<cfqueryparam value="127.0.0.1" cfsqltype="cf_sql_varchar" />, 
				<cfqueryparam value="#blogFusionEntries.entryStatus#" cfsqltype="cf_sql_tinyint" />
			)
		</cfquery>
		
		<cfquery name="insertEntryMapping" datasource="#machblogDSN#">
			insert into old_entry_to_new_entry (
				old_entry_id, 
				new_entry_id
			) values (
				<cfqueryparam value="#blogFusionEntries.entryID#" cfsqltype="cf_sql_integer" />, 
				<cfqueryparam value="#newEntryID#" cfsqltype="cf_sql_char" />
			)
		</cfquery>
		
		<!--- get the category IDs from BlogFusion and convert to MachBlog categories --->
		<cfquery name="getCategoryIDs" dbtype="query">
			select listCatIDFK as oldCategoryID 
			from blogFusionEntryCats 
			where listEntryIDFK = <cfqueryparam value="#blogFusionEntries.entryID#" cfsqltype="cf_sql_integer" />
		</cfquery>
		
		<cfloop query="getCategoryIDs">
			<!--- get the new category ID --->
			<cfquery name="getNewCategoryID" datasource="#machblogDSN#">
				select new_category_id as newCategoryID
				from old_cat_to_new_cat 
				where old_category_id = <cfqueryparam value="#getCategoryIDs.oldCategoryID#">
			</cfquery>
			
			<cfquery name="insertEntryCategory" datasource="#machblogDSN#">
				insert into machblog_entry_category (
					entry_id, 
					category_id
				) values (
					<cfqueryparam value="#newEntryID#" cfsqltype="cf_sql_char" />, 
					<cfqueryparam value="#getNewCategoryID.newCategoryID#" cfsqltype="cf_sql_char" />
				)
			</cfquery>
		</cfloop>
	</cftransaction>
</cfloop>

Entries and categories inserted.<br />
<br />
Retrieving comments from BlogFusion ...<br />
<cfflush />

<cfquery name="blogFusionComments" datasource="#blogfusionDSN#">
	select 	entryIDFK, 
			comment, 
			email, 
			name, 
			URL, 
			entryDateComments, 
			IPvalue, 
			commentStatus, 
			commentVerifyVar 
	from 	comments 
	order by entryDateComments asc
</cfquery>

Retrieved <cfoutput>#blogFusionComments.recordCount#</cfoutput> comments from BlogFusion.<br />
<br />

Inserting comments into MachBlog ...<br />
<cfflush />

<cfloop query="blogFusionComments">
	<cfset commentID = createUUID() />
	
	<!--- get the new entry id from the old one --->
	<cfquery name="getNewEntryID" datasource="#machblogDSN#">
		select 	new_entry_id as newEntryID
		from 	old_entry_to_new_entry 
		where 	old_entry_id = <cfqueryparam value="#blogFusionComments.entryIDFK#" cfsqltype="cf_sql_integer" />
	</cfquery>
	
	<cfquery name="insertComment" datasource="#machblogDSN#">
		insert into machblog_comment (
			comment_id, 
			entry_id, 
			name, 
			email, 
			url, 
			comment, 
			is_subscribed, 
			subscription_only, 
			subscriber_email_sent, 
			dt_created, 
			ip_created, 
			is_active
		) values (
			<cfqueryparam value="#commentID#" cfsqltype="cf_sql_char" />, 
			<cfqueryparam value="#getNewEntryID.newEntryID#" cfsqltype="cf_sql_char" />, 
			<cfqueryparam value="#trim(blogFusionComments.name)#" cfsqltype="cf_sql_varchar" />, 
			<cfqueryparam value="#trim(blogFusionComments.email)#" cfsqltype="cf_sql_varchar" null="#not len(blogFusionComments.email)#" />, 
			<cfqueryparam value="#trim(blogFusionComments.URL)#" cfsqltype="cf_sql_varchar" null="#not len(blogFusionComments.URL)#" />, 
			<cfqueryparam value="#trim(blogFusionComments.comment)#" cfsqltype="cf_sql_longvarchar" />, 
			<cfqueryparam value="0" cfsqltype="cf_sql_tinyint" />, 
			<cfqueryparam value="0" cfsqltype="cf_sql_tinyint" />, 
			<cfqueryparam value="1" cfsqltype="cf_sql_tinyint" />, 
			<cfqueryparam value="#i18nUtils.toEpoch(now())#" cfsqltype="cf_sql_bigint" />, 
			<cfqueryparam value="#trim(blogFusionComments.IPvalue)#" cfsqltype="cf_sql_varchar" />, 
			<cfqueryparam value="#blogFusionComments.commentStatus#" cfsqltype="cf_sql_tinyint" />
		)
	</cfquery>
</cfloop>

Completed inserting comments into MachBlog.<br />
<br />

Getting BlogFusion blog-wide subscribers ...<br />
<cfflush />

<cfquery name="blogFusionSubscribers" datasource="#blogfusionDSN#">
	select 	subBlogEmail, 
			subBlogIP, 
			subBlogDate 
	from 	subBlog 
	order by subBlogDate asc
</cfquery>

Retrieved <cfoutput>#blogFusionSubscribers.recordCount#</cfoutput> subscribers from BlogFusion.<br />
<br />

Inserting blog-wide subscribers into MachBlog ...<br />
<cfflush />

<cfloop query="blogFusionSubscribers">
	<cfset subscriberID = createUUID() />
	
	<cfquery name="insertSubscriber" datasource="#machblogDSN#">
		insert into machblog_subscriber (
			subscriber_id, 
			subscriber_email, 
			dt_created, 
			ip_created, 
			is_active
		) values (
			<cfqueryparam value="#subscriberID#" cfsqltype="cf_sql_char" />, 
			<cfqueryparam value="#trim(blogFusionSubscribers.subBlogEmail)#" cfsqltype="cf_sql_varchar" />, 
			<cfqueryparam value="#i18nUtils.toEpoch(blogFusionSubscribers.subBlogDate)#" cfsqltype="cf_sql_bigint" />, 
			<cfqueryparam value="127.0.0.1" cfsqltype="cf_sql_varchar" />, 
			<cfqueryparam value="1" cfsqltype="cf_sql_tinyint" />
		)
	</cfquery>
</cfloop>

Completed migrating subscribers.<br />
<br />

<strong>Migration complete.</strong>
